<?php

/**
 * 导出Excel文件
 * ==================================================================
*/

define('IN_ECS', true);

require(dirname(__FILE__) . '/includes/init.php');
require_once(ROOT_PATH . 'includes/lib_order.php');
require_once(ROOT_PATH . 'includes/lib_goods.php');

require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
$PHPExcel = new PHPExcel();

/*------------------------------------------------------ */
//-- 订单对账导出Excel文件
/*------------------------------------------------------ */
if ($_REQUEST['act'] == 'order_export')
{
		/*获取查询参数值*/
		$filter['suppliers_id'] = $_REQUEST['suppliers_id'];
        $filter['order_sn'] = empty($_REQUEST['order_sn']) ? '' : trim($_REQUEST['order_sn']);
        $filter['consignee'] = empty($_REQUEST['consignee']) ? '' : trim($_REQUEST['consignee']);
        $filter['email'] = empty($_REQUEST['email']) ? '' : trim($_REQUEST['email']);
        $filter['address'] = empty($_REQUEST['address']) ? '' : trim($_REQUEST['address']);
        $filter['zipcode'] = empty($_REQUEST['zipcode']) ? '' : trim($_REQUEST['zipcode']);
        $filter['tel'] = empty($_REQUEST['tel']) ? '' : trim($_REQUEST['tel']);
        $filter['mobile'] = empty($_REQUEST['mobile']) ? 0 : intval($_REQUEST['mobile']);
        $filter['country'] = empty($_REQUEST['country' ]) ? 0 : intval($_REQUEST['country']);
        $filter['province'] = empty($_REQUEST['province']) ? 0 : intval($_REQUEST['province']);
        $filter['city'] = empty($_REQUEST['city']) ? 0 : intval($_REQUEST['city']);
        $filter['district'] = empty($_REQUEST['district']) ? 0 : intval($_REQUEST['district']);
        $filter['shipping_id'] = empty($_REQUEST['shipping_id']) ? 0 : intval($_REQUEST['shipping_id']);
        $filter['pay_id'] = empty($_REQUEST['pay_id']) ? 0 : intval($_REQUEST['pay_id']);
        $filter['order_status'] = isset($_REQUEST['order_status']) ? intval($_REQUEST['order_status']) : -1;
        $filter['shipping_status'] = isset($_REQUEST['shipping_status']) ? intval($_REQUEST['shipping_status']) : -1;
        $filter['pay_status'] = isset($_REQUEST['pay_status']) ? intval($_REQUEST['pay_status']) : -1;
        $filter['user_id'] = empty($_REQUEST['user_id']) ? 0 : intval($_REQUEST['user_id']);
        $filter['user_name'] = empty($_REQUEST['user_name']) ? '' : trim($_REQUEST['user_name']);
        $filter['composite_status'] = isset($_REQUEST['composite_status']) ? intval($_REQUEST['composite_status']) : -1;
        $filter['group_buy_id'] = isset($_REQUEST['group_buy_id']) ? intval($_REQUEST['group_buy_id']) : 0;

        $filter['start_time'] = empty($_REQUEST['start_time']) ? '' : (strpos($_REQUEST['start_time'], '-') > 0 ?  local_strtotime($_REQUEST['start_time']) : $_REQUEST['start_time']);

        $filter['end_time'] = empty($_REQUEST['end_time']) ? '' : (strpos($_REQUEST['end_time'], '-') > 0 ?  local_strtotime($_REQUEST['end_time']) : $_REQUEST['end_time']);

        /* Y-新增 6.28 付款时间 */
        $filter['start_time_a'] = empty($_REQUEST['start_time_a']) ? '' : (strpos($_REQUEST['start_time_a'], '-') > 0 ?  local_strtotime($_REQUEST['start_time_a']) : $_REQUEST['start_time_a']);

        $filter['end_time_a'] = empty($_REQUEST['end_time_a']) ? '' : (strpos($_REQUEST['end_time_a'], '-') > 0 ?  local_strtotime($_REQUEST['end_time_a']) : $_REQUEST['end_time_a']);

        /* Y-新增 6.28 供应商发货时间 */

      	/*$filter['start_time_b']  = empty($_REQUEST['start_time_b']) ? '' : (strpos($_REQUEST['start_time_b'], '-') > 0 ?  local_strtotime($_REQUEST['start_time_b']) : $_REQUEST['start_time_b']);
		$filter['end_time_b'] =  empty($_REQUEST['end_time_b']) ? '' : (strpos($_REQUEST['end_time_b'], '-') > 0 ?  local_strtotime($_REQUEST['end_time_b']) : $_REQUEST['end_time_b']);*/

        /* Y-新增 6.28 支付货款时间 */

        /* 	$filter['start_time_c'] =  empty($_REQUEST['start_time_c']) ? '' : (strpos($_REQUEST['start_time_c'], '-') > 0 ?  local_strtotime($_REQUEST['start_time_c']) : $_REQUEST['start_time_c']);
		$filter['end_time_c']  =  empty($_REQUEST['end_time_c']) ? '' : (strpos($_REQUEST['end_time_c'], '-') > 0 ?  local_strtotime($_REQUEST['end_time_c']) : $_REQUEST['end_time_c']);*/

        /* Y-新增 6.28 订单完成时间 */

        $filter['start_time_d'] = empty($_REQUEST['start_time_d']) ? '' : (strpos($_REQUEST['start_time_d'], '-') > 0 ?  local_strtotime($_REQUEST['start_time_d']) : $_REQUEST['start_time_d']);

        $filter['end_time_d'] = empty($_REQUEST['end_time_d']) ? '' : (strpos($_REQUEST['end_time_d'], '-') > 0 ?  local_strtotime($_REQUEST['end_time_d']) : $_REQUEST['end_time_d']);

         /* 分页大小 */
        $filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);

	    /*取得保存数据的数组*/
		$olists = empty($_REQUEST['lists']) ? '' : $_REQUEST['lists'];

		/*查询数据 一次只取100条数据*/
		$lists = get_order_export($filter);

		/*合并*/
		/*之前的值先反序列化*/
		if(!empty($olists)){

			$olists = unserialize(stripslashes($olists));

			/*file_put_contents('log1.php',"<?php\r\nreturn ".var_export($olists,true)."?>",FILE_APPEND);*/
			$newlists = array_merge($olists,$lists['orders']);
			/*file_put_contents('log2.php',"<?php\r\nreturn ".var_export($newlists,true)."?>",FILE_APPEND);*/

		}else{

			$newlists = $lists['orders'];

		}

        /*新的值序列化*/
		$newlists = serialize($newlists);

		$filter['page'] = $filter['page']+1;

		/*是否应该跳转*/
		if(($filter['page']) <= $lists['filter']['page_count']){

				$smarty->assign('yes', 1);

		}else{

				$smarty->assign('yes', 0);
		}

		$smarty->assign('lists', $newlists);

		$smarty->assign('filter', $filter);

		$smarty->assign('act', "order_export");

		$smarty->display('export.htm');

}

elseif ($_REQUEST['act'] == 'export')
{
	$lists = $_REQUEST['lists'];

	/*反序列化*/
	$order_sn_list = unserialize(stripslashes($lists));

	if (empty($order_sn_list))
	{
		// sys_msg("查询数据为空");
		echo '<script>
			 alert("查询数据为空");
			 window.close();
			 </script>';
		exit;	
	}

	/*数据导出Excel文件*/
	$html = '';

	//设置excel属性基本信息
	$PHPExcel->getProperties()->setCreator("Neo")
	->setLastModifiedBy("Neo")
	->setTitle("添米商城对账表")
	->setSubject("订单列表")
	->setDescription("")
	->setKeywords("订单列表")
	->setCategory("");
	$PHPExcel->setActiveSheetIndex(0);
	$PHPExcel->getActiveSheet()->setTitle("订单列表");
	$PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);
	$PHPExcel->getActiveSheet()->getStyle('A1:Y1')->getFont()->setBold(true);

	//设置单元格边框
	$styleArray = array(
		'borders' => array(
			'allborders' => array(
				//'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
				'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
				//'color' => array('argb' => 'FFFF0000'),
			),
		),
	);

	//表格宽度
	$PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);//订单编号
	$PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);//下单时间
	$PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);//下单人
	$PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);//商品编号
	$PHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(18);//商品单价
	$PHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);//购买数量
	$PHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);//市场价
	$PHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);//实际售价
	$PHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);//额外折扣
	$PHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);//配送费用
	$PHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);//使用余额
	$PHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);//使用积分
	$PHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);//使用红包
	$PHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(25);//实际付款金额
	$PHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);//付款时间
	$PHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);//支付方式
	$PHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(30);//订单状态
	$PHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);//订单完成时间
	$PHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);//商品供应商
	$PHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);//商品供应价
	$PHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(15);//商品数量
	$PHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(15);//采购单价
	$PHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(15);//商品采购总价
	$PHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(15);//供应商发货时间
	$PHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(15);//货款支付时间


	//表格标题
	$PHPExcel->getActiveSheet()->setCellValue('A1', '订单号');
	$PHPExcel->getActiveSheet()->setCellValue('B1', '下单时间');
	$PHPExcel->getActiveSheet()->setCellValue('C1', '下单人');
	$PHPExcel->getActiveSheet()->setCellValue('D1', '商品编号');
	$PHPExcel->getActiveSheet()->setCellValue('E1', '商品单价');
	$PHPExcel->getActiveSheet()->setCellValue('F1', '购买数量');
	$PHPExcel->getActiveSheet()->setCellValue('G1', '市场价');
	$PHPExcel->getActiveSheet()->setCellValue('H1', '实际售价');
	$PHPExcel->getActiveSheet()->setCellValue('I1', '额外折扣');
	$PHPExcel->getActiveSheet()->setCellValue('J1', '配送费用');
	$PHPExcel->getActiveSheet()->setCellValue('K1', '使用余额');
	$PHPExcel->getActiveSheet()->setCellValue('L1', '使用积分');
	$PHPExcel->getActiveSheet()->setCellValue('M1', '使用红包');
	$PHPExcel->getActiveSheet()->setCellValue('N1', '实际付款金额');
	$PHPExcel->getActiveSheet()->setCellValue('O1', '付款时间');
	$PHPExcel->getActiveSheet()->setCellValue('P1', '支付方式');
	$PHPExcel->getActiveSheet()->setCellValue('Q1', '订单状态');
	$PHPExcel->getActiveSheet()->setCellValue('R1', '订单完成时间');
	$PHPExcel->getActiveSheet()->setCellValue('S1', '商品供应商');
	$PHPExcel->getActiveSheet()->setCellValue('T1', '商品供应价');
	$PHPExcel->getActiveSheet()->setCellValue('U1', '商品数量');
	$PHPExcel->getActiveSheet()->setCellValue('V1', '采购单价');
	$PHPExcel->getActiveSheet()->setCellValue('W1', '商品采购总价');
	$PHPExcel->getActiveSheet()->setCellValue('X1', '供应商发货时间');
	$PHPExcel->getActiveSheet()->setCellValue('Y1', '货款支付时间');

    $hang = 2;
	foreach ($order_sn_list as $order_sn) {

		$shuliang   = 0;
		$chanpin    = $hang;

		foreach($order_sn['goods_list'] as $k=>$row){

			$shuliang = $shuliang + 1;

			if(empty($row['shop_price'])){
				$row['shop_price'] = '0';
			}

			if($row['suppliers_id'] == 0){
				$row['suppliers_id']='本店商品';
			}elseif($row['suppliers_id'] == 1){
				$row['suppliers_id']='北京供应商';
			}else{
				$row['suppliers_id']= '上海供应商';
			}

			//var_dump($order);die;
			//输出订单的商品，由于可能一个人购买多个商品，所以在这先输出了
			$PHPExcel->getActiveSheet()->setCellValue('D' . $chanpin, $row['goods_sn']);
			$PHPExcel->getActiveSheet()->setCellValue('E' . $chanpin, $row['shop_price'].'元');
			$PHPExcel->getActiveSheet()->setCellValue('F' . $chanpin, $row['goods_number']);
			$PHPExcel->getActiveSheet()->setCellValue('G' . $chanpin, $row['market_price'].'元');
			$PHPExcel->getActiveSheet()->setCellValue('H' . $chanpin, $row['goods_price'].'元');
			$PHPExcel->getActiveSheet()->setCellValue('U' . $chanpin, $row['goods_number']);
			$PHPExcel->getActiveSheet()->setCellValue('S' . $chanpin, "");
			$PHPExcel->getActiveSheet()->setCellValue('V' . $chanpin, "");

			$chanpin = $chanpin + 1;
		}

		for ($kk = $hang; $kk < ($hang + $shuliang); $kk++) {
			//合并单元格
			$PHPExcel->getActiveSheet()->mergeCells('A' . $hang . ':A' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('B' . $hang . ':B' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('C' . $hang . ':C' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('I' . $hang . ':I' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('J' . $hang . ':J' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('K' . $hang . ':K' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('L' . $hang . ':L' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('M' . $hang . ':M' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('N' . $hang . ':N' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('O' . $hang . ':O' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('P' . $hang . ':P' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('Q' . $hang . ':Q' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('R' . $hang . ':R' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('T' . $hang . ':T' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('W' . $hang . ':W' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('X' . $hang . ':X' . $kk);
			$PHPExcel->getActiveSheet()->mergeCells('Y' . $hang . ':Y' . $kk);
		}

		/*订单状态*/
		if($order_sn['order_status'] == 0){
			$order_status='待确认';
		}elseif($order_sn['order_status'] == 1){
			$order_status= '已确认';
		}elseif($order_sn['order_status'] == 2){
			$order_status= '已取消';
		}elseif($order_sn['order_status'] == 3){
			$order_status= '无效';
		}elseif($order_sn['order_status'] == 4){
			$order_status= '退货';
		}elseif($order_sn['order_status'] == 6){
			$order_status= '部分发货';
		}

		/*付款状态*/
		if($order_sn['pay_status'] == 0){
			$pay_status='未付款';
		}elseif($order_sn['pay_status'] == 1){
			$pay_status='付款中';
		}elseif($order_sn['pay_status'] == 2){
			$pay_status='已付款';
		}

        /*发货状态*/
		if($order_sn['shipping_status'] == 0){
			$shipping_status='未发货';
		}elseif($order_sn['shipping_status'] == 1){
			$shipping_status='已发货';
		}elseif($order_sn['shipping_status'] == 2){
			$shipping_status='已收货';
		}elseif($order_sn['shipping_status'] == 4){
			$shipping_status='退货';
		}


        if(!$order_sn['buyer']){
		   $order_sn['buyer']	='匿名购买';
		}
		if(empty($order_sn['surplus'])){
		   $order_sn['surplus']	='0';
		}

		$PHPExcel->getActiveSheet()->setCellValue('A' . ($hang), $order_sn['order_sn']." ");//加个空格，防止时间戳被转换
		$PHPExcel->getActiveSheet()->setCellValue('B' . ($hang), $order_sn['short_order_time']);
		$PHPExcel->getActiveSheet()->setCellValue('C' . ($hang), $order_sn['buyer']);
		$PHPExcel->getActiveSheet()->setCellValue('I' . ($hang), $order_sn['discount']);
		$PHPExcel->getActiveSheet()->setCellValue('J' . ($hang), $order_sn['shipping_fee'].'元');
		$PHPExcel->getActiveSheet()->setCellValue('K' . ($hang), $order_sn['surplus'].'元');
		$PHPExcel->getActiveSheet()->setCellValue('L' . ($hang), $order_sn['integral']);
		$PHPExcel->getActiveSheet()->setCellValue('M' . ($hang), $order_sn['bonus'].'元');
		$PHPExcel->getActiveSheet()->setCellValue('N' . ($hang), $order_sn['total_fee'].'元');
		$PHPExcel->getActiveSheet()->setCellValue('O' . ($hang), $order_sn['short_pay_time']);
		$PHPExcel->getActiveSheet()->setCellValue('P' . ($hang), $order_sn['pay_name']);
		$PHPExcel->getActiveSheet()->setCellValue('Q' . ($hang), $order_status." ".$pay_status." ".$shipping_status);
		$PHPExcel->getActiveSheet()->setCellValue('R' . ($hang), $order_sn['short_affirm_time']);
		$PHPExcel->getActiveSheet()->setCellValue('T' . ($hang), $order_sn['']);
		$PHPExcel->getActiveSheet()->setCellValue('W' . ($hang), $order_sn['']);
		$PHPExcel->getActiveSheet()->setCellValue('X' . ($hang), $order_sn['']);
		$PHPExcel->getActiveSheet()->setCellValue('Y' . ($hang), $order_sn['']);

		$hang = $hang + $shuliang;
    }

	//设置单元格边框
	$PHPExcel->getActiveSheet()->getStyle('A1:Y'.$hang)->applyFromArray($styleArray);
	//设置自动换行
	$PHPExcel->getActiveSheet()->getStyle('A1:Y'.$hang)->getAlignment()->setWrapText(true);
	//设置字体大小
	$PHPExcel->getActiveSheet()->getStyle('A1:Y'.$hang)->getFont()->setSize(12);
	//垂直居中
	$PHPExcel->getActiveSheet()->getStyle('A1:Y'.$hang)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	//水平居中
	$PHPExcel->getActiveSheet()->getStyle('A1:Y'.$hang)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	

	$outputFileName = '订单对账数据表' . date("Y/m/d") . '.xls';
	$xlsWriter = new \PHPExcel_Writer_Excel5($PHPExcel);
	header("Content-Type: application/force-download");
	header("Content-Type: application/octet-stream");
	header("Content-Type: application/download");
	header('Content-Disposition:inline;filename="' . $outputFileName . '"');
	header("Content-Transfer-Encoding: binary");
	header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
	header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	header("Pragma: no-cache");
	$xlsWriter->save("php://output");
	echo file_get_contents($outputFileName);
	exit;

}
elseif($_REQUEST['act'] == 'ajaxs'){

	print_r($_REQUEST);
	die;
	
}
/*------------------------------------------------------ */
//-- 退货对账
/*------------------------------------------------------ */

if ($_REQUEST['act'] == 'refund_goods')
{

         /* 分页大小 */
        $filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);

	    /*取得保存数据的数组*/
		$olists = empty($_REQUEST['lists']) ? '' : $_REQUEST['lists'];

		/*查询数据 一次只取100条数据*/
		$lists = get_refund_goods($filter);

		/*合并*/
		/*之前的值先反序列化*/
		if(!empty($olists)){

			$olists = unserialize(stripslashes($olists));

			/*file_put_contents('log1.php',"<?php\r\nreturn ".var_export($olists,true)."?>",FILE_APPEND);*/
			$newlists = array_merge($olists,$lists['back']);
			/*file_put_contents('log2.php',"<?php\r\nreturn ".var_export($newlists,true)."?>",FILE_APPEND);*/

		}else{

			$newlists = $lists['back'];

		}

        /*新的值序列化*/
		$newlists = serialize($newlists);

		$filter['page'] = $filter['page']+1;

		/*是否应该跳转*/
		if(($filter['page']) <= $lists['filter']['page_count']){

			$smarty->assign('yes', 1);

		}else{

			$smarty->assign('yes', 0);
		}

		$smarty->assign('lists', $newlists);

		$smarty->assign('filter', $filter);

		$smarty->assign('act', "refund_goods");

		$smarty->display('export.htm');

}

elseif ($_REQUEST['act'] == 'goods')
{

	$lists = $_REQUEST['lists'];

	/*反序列化*/
	$refund_goods_list = unserialize(stripslashes($lists));

	/*获取退货订单数据*/
	// $refund_goods_list = get_refund_goods();

	if (empty($refund_goods_list))
	{
		echo '<script>
			 alert("查询数据为空");
			 window.close();
			 </script>';
		// sys_msg("查询数据为空");
		exit;
	}

	// 实例化excel类
    $objPHPExcel = new PHPExcel();
    // 操作第一个工作表
    $objPHPExcel->setActiveSheetIndex(0);
    // 设置sheet名
    $objPHPExcel->getActiveSheet()->setTitle('退款退货对账数据表');
    // 设置表格宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);//发货单流水号
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);//订单号
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);//申请人
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);//申请时间
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);//退货数量
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);//实际退货总金额
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);//退货销售单价
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);//退货销售金额
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);//采购单价
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);//采购总金额
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);//退货完成时间
	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);//退货商品供应商


    // 列名表头文字加粗
    $objPHPExcel->getActiveSheet()->getStyle('A1:L1')->getFont()->setBold(true);
    // 列表头文字居中
    $objPHPExcel->getActiveSheet()->getStyle('A1:L1')->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    // 列名赋值
    $objPHPExcel->getActiveSheet()->setCellValue('A1', '发货单流水号');
    $objPHPExcel->getActiveSheet()->setCellValue('B1', '订单号');
    $objPHPExcel->getActiveSheet()->setCellValue('C1', '申请人');
    $objPHPExcel->getActiveSheet()->setCellValue('D1', '申请时间');
    $objPHPExcel->getActiveSheet()->setCellValue('E1', '退货数量');
    $objPHPExcel->getActiveSheet()->setCellValue('F1', '实际退货总金额');
	$objPHPExcel->getActiveSheet()->setCellValue('G1', '退货销售单价');
	$objPHPExcel->getActiveSheet()->setCellValue('H1', '退货销售金额');
	$objPHPExcel->getActiveSheet()->setCellValue('I1', '采购单价');
	$objPHPExcel->getActiveSheet()->setCellValue('J1', '采购总金额');
	$objPHPExcel->getActiveSheet()->setCellValue('K1', '退货完成时间');
	$objPHPExcel->getActiveSheet()->setCellValue('L1', '退货商品供应商');

    // 数据起始行
    $row_num = 2;
    // 向每行单元格插入数据
    foreach($refund_goods_list as $k=>$value)

    {

        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'L' . $row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'L' . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置价格为数字格式
        $objPHPExcel->getActiveSheet()->getStyle('F' . $row_num . ':' . 'J' . $row_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['delivery_sn']." ");

        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['order_sn']." ");
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['user_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['add_time']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['goods_nums']);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['back_amount']);
		$objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['goods_price']);
		$objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, $value['goods_amount']);
		$objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, "0.00");
		$objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, "0.00");
		$objPHPExcel->getActiveSheet()->setCellValue('K' . $row_num, $value['return_time']);
		$objPHPExcel->getActiveSheet()->setCellValue('L' . $row_num, "退货供应商");

        $row_num++;
    }
    $outputFileName = '退款退货对账数据表' . date("Y/m/d") . '.xls';
    $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);
	exit;

}
elseif($_REQUEST['act'] == 'ajaxs'){

	print_r($_REQUEST);
	die;
	
}
/*------------------------------------------------------ */
//-- 充值提现对账
/*------------------------------------------------------ */

if ($_REQUEST['act'] == 'account_export')
{
    /*获取查询参数值*/

	$filter['suppliers_id'] = $_REQUEST['suppliers_id'];

	$filter['keywords']  = empty($_REQUEST['keyword']) ? '' : trim($_REQUEST['keyword']);

	$filter['process_type']  = isset($_REQUEST['process_type']) ? intval($_REQUEST['process_type']) : -1;

	$filter['payment'] = empty($_REQUEST['payment']) ? '' : trim($_REQUEST['payment']);

	$filter['is_paid']  = isset($_REQUEST['is_paid']) ? intval($_REQUEST['is_paid']) : -1;

	$filter['start_date']  = empty($_REQUEST['start_date']) ? '' : (strpos($_REQUEST['start_date'], '-') > 0 ?  local_strtotime($_REQUEST['start_date']) : $_REQUEST['start_date']);

	$filter['end_date']  = empty($_REQUEST['end_date']) ? '' : (strpos($_REQUEST['end_date'], '-') > 0 ?  local_strtotime($_REQUEST['end_date']) : $_REQUEST['end_date']);

	/* 分页大小 */
	$filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);

	/*取得保存数据的数组*/
	$olists = empty($_REQUEST['lists']) ? '' : $_REQUEST['lists'];

	/*查询数据 一次只取100条数据*/
	$lists = get_account_export($filter);

	/*合并*/
		/*之前的值先反序列化*/
		if(!empty($olists)){

			$olists = unserialize(stripslashes($olists));

			/*file_put_contents('log1.php',"<?php\r\nreturn ".var_export($olists,true)."?>",FILE_APPEND);*/
			$newlists = array_merge($olists,$lists['row']);
			/*file_put_contents('log2.php',"<?php\r\nreturn ".var_export($newlists,true)."?>",FILE_APPEND);*/

		}else{

			$newlists = $lists['row'];

		}

        /*新的值序列化*/
		$newlists = serialize($newlists);

		$filter['page'] = $filter['page']+1;

		/*是否应该跳转*/
		if(($filter['page']) <= $lists['filter']['page_count']){

				$smarty->assign('yes', 1);

		}else{

				$smarty->assign('yes', 0);
		}

		$smarty->assign('lists', $newlists);

		$smarty->assign('filter', $filter);

		$smarty->assign('act', "account_export");

		$smarty->display('export.htm');

}
elseif ($_REQUEST['act'] == 'exporta')
{

		$lists = $_REQUEST['lists'];

		/*反序列化*/
		$account_export_list = unserialize(stripslashes($lists));

		/*获取退货订单数据*/
		// $refund_goods_list = get_refund_goods();

		if (empty($account_export_list))
		{
			echo '<script>
			 alert("查询数据为空");
			 window.close();
			 </script>';
			// sys_msg("查询数据为空");
			exit;
		}

		// 实例化excel类
		$objPHPExcel = new PHPExcel();
		// 操作第一个工作表
		$objPHPExcel->setActiveSheetIndex(0);
		// 设置sheet名
		$objPHPExcel->getActiveSheet()->setTitle('充值提现列表');
		// 设置表格宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
		// 列名表头文字加粗
		$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFont()->setBold(true);
		// 列表头文字居中
		$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getAlignment()
			->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 列名赋值
		$objPHPExcel->getActiveSheet()->setCellValue('A1', '会员名称');
		$objPHPExcel->getActiveSheet()->setCellValue('B1', '操作日期');
		$objPHPExcel->getActiveSheet()->setCellValue('C1', '类型');
		$objPHPExcel->getActiveSheet()->setCellValue('D1', '金额');
		$objPHPExcel->getActiveSheet()->setCellValue('E1', '支付方式');
		$objPHPExcel->getActiveSheet()->setCellValue('F1', '到款状态');
		$objPHPExcel->getActiveSheet()->setCellValue('G1', '操作员');

		// 数据起始行
		$row_num = 2;
    // 向每行单元格插入数据
    foreach( $account_export_list as $k=>$value)
    {

        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'G' . $row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'G' . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置价格为数字格式
        $objPHPExcel->getActiveSheet()->getStyle('D' . $row_num . ':' . 'D' . $row_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

		if($value['is_paid']==1){
		   $value['is_paid']='已完成';
	    }elseif($value['is_paid']==2){
		   $value['is_paid']='取消';
	    }else{
		   $value['is_paid']='未确认';
		}
		$value['add_time']=local_date('Y-m-d H:i:s',$value['add_time']);

		$value['process_type']=$value['process_type']?'提现':'充值';

        if(!$value['user_name']){
		   $value['user_name']	='匿名购买';
		}
        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['user_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['add_time']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['process_type']);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['amount']);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['payment']);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['is_paid']);
		$objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['admin_user']);

        $row_num++;
    }
    $outputFileName = '充值提现数据表' . date("Y/m/d") . '.xls';
    $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);

}
elseif($_REQUEST['act'] == 'ajaxs'){

	print_r($_REQUEST);
	die;

}

/*------------------------------------------------------ */
//-- 充值及资金使用对账
/*------------------------------------------------------ */

if ($_REQUEST['act'] == 'acc_export')
{
    /*获取查询参数值*/

	$filter['user_id'] = empty($_REQUEST['user_id']) ? '' : trim($_REQUEST['user_id']);

	/*充值时间段*/
	$filter['start_time'] = empty($_REQUEST['start_time']) ? '' : (strpos($_REQUEST['start_time'], '-') > 0 ?  local_strtotime($_REQUEST['start_time']) : $_REQUEST['start_time']);
	$filter['end_time'] = empty($_REQUEST['end_time']) ? '' : (strpos($_REQUEST['end_time'], '-') > 0 ?  local_strtotime($_REQUEST['end_time']) : $_REQUEST['end_time']);

	/*充值方式*/
	$filter['pay_code'] = empty($_REQUEST['pay_code']) ? '' : trim($_REQUEST['pay_code']);

	/*使用时间段*/
	$filter['start_change_time'] = empty($_REQUEST['start_change_time']) ? '' : (strpos($_REQUEST['start_change_time'], '-') > 0 ?  local_strtotime($_REQUEST['start_change_time']) : $_REQUEST['start_change_time']);

	$filter['end_change_time'] = empty($_REQUEST['end_change_time']) ? '' : (strpos($_REQUEST['end_change_time'], '-') > 0 ?  local_strtotime($_REQUEST['end_change_time']) : $_REQUEST['end_change_time']);

	/* 分页大小 */
	$filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);

	/*取得保存数据的数组*/
	$olists = empty($_REQUEST['lists']) ? '' : $_REQUEST['lists'];

	/*查询数据 一次只取100条数据*/
	$lists = get_acc_export($filter);

	/*合并*/
		/*之前的值先反序列化*/
		if(!empty($olists)){

			$olists = unserialize(stripslashes($olists));

			/*file_put_contents('log1.php',"<?php\r\nreturn ".var_export($olists,true)."?>",FILE_APPEND);*/
			$newlists = array_merge($olists,$lists['list']);
			/*file_put_contents('log2.php',"<?php\r\nreturn ".var_export($newlists,true)."?>",FILE_APPEND);*/

		}else{

			$newlists = $lists['list'];

		}

        /*新的值序列化*/
		$newlists = serialize($newlists);

		$filter['page'] = $filter['page']+1;

		/*是否应该跳转*/
		if(($filter['page']) <= $lists['filter']['page_count']){

				$smarty->assign('yes', 1);

		}else{

				$smarty->assign('yes', 0);
		}

		$smarty->assign('lists', $newlists);

		$smarty->assign('filter', $filter);

		$smarty->assign('act', "acc_export");

		$smarty->display('export.htm');

}
elseif ($_REQUEST['act'] == 'exportb')
{

		$lists = $_REQUEST['lists'];

		/*反序列化*/
		$acc_export_list = unserialize(stripslashes($lists));

		/*获取退货订单数据*/
		// $refund_goods_list = get_refund_goods();

		if (empty($acc_export_list))
			
		{	
			echo '<script>
			 alert("查询数据为空");
			 window.close();
			 </script>';
			// sys_msg("查询数据为空");

			exit;
		}

		// 实例化excel类
		$objPHPExcel = new PHPExcel();
		// 操作第一个工作表
		$objPHPExcel->setActiveSheetIndex(0);
		// 设置sheet名
		$objPHPExcel->getActiveSheet()->setTitle('充值及资金使用列表');
		// 设置表格宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
		// 列名表头文字加粗
		$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
		// 列表头文字居中
		$objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()
			->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 列名赋值
		$objPHPExcel->getActiveSheet()->setCellValue('A1', '交易流水号');
		$objPHPExcel->getActiveSheet()->setCellValue('B1', '客户编号');
		$objPHPExcel->getActiveSheet()->setCellValue('C1', '客户姓名');
		$objPHPExcel->getActiveSheet()->setCellValue('D1', '充值时间');
		$objPHPExcel->getActiveSheet()->setCellValue('E1', '充值金额');
		$objPHPExcel->getActiveSheet()->setCellValue('F1', '充值方式');
		$objPHPExcel->getActiveSheet()->setCellValue('G1', '使用时间');
		$objPHPExcel->getActiveSheet()->setCellValue('H1', '订单号');
		$objPHPExcel->getActiveSheet()->setCellValue('I1', '使用金额');
		$objPHPExcel->getActiveSheet()->setCellValue('J1', '账户余额');

		// 数据起始行
		$row_num = 2;
    // 向每行单元格插入数据
    foreach( $acc_export_list as $k=>$value)
    {
		// print_r($value);
		// die;

        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'J' . $row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'J' . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	
		// 设置价格为数字格式
        $objPHPExcel->getActiveSheet()->getStyle('E' . $row_num . ':' . 'E' . $row_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
		
		$objPHPExcel->getActiveSheet()->getStyle('I' . $row_num . ':' . 'J' . $row_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
		
        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['log_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['user_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['user_name']);

		if($value['change_type'] == 0){
			$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['change_time']);
			$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['user_money']);
			$objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['pay_code']);
		}else{
			$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['']);
			$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['']);
			$objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['']);
		}

		if($value['change_type'] == 99){
			$objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['change_time']);
			$objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, $value['order_sn']);
			$objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, $value['user_money']);
		}else{
			$objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['']);
			$objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, $value['']);
			$objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, $value['']);
		}

		$objPHPExcel->getActiveSheet()->setCellValue('J' . $row_num, $value['new_money']);

        $row_num++;
    }
    $outputFileName = '充值及资金使用列表' . date("Y/m/d") . '.xls';
    $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);

}
elseif($_REQUEST['act'] == 'ajaxs'){

	print_r($_REQUEST);
	die;

}

/*------------------------------------------------------ */
//-- 余额,积分,红包对账
/*------------------------------------------------------ */

if ($_REQUEST['act'] == 'point_query')
{

	/*类型 默认为1 余额 2积分 3红包*/
	$type = isset($_REQUEST['type']) ? intval($_REQUEST['type']) : 1;
	
	/*查询数据*/
	$filter['type'] = $type;
	$filter['start_time'] = empty($_REQUEST['start_time']) ? '' : (strpos($_REQUEST['start_time'], '-') > 0 ?  local_strtotime($_REQUEST['start_time']) : $_REQUEST['start_time']);
		
    $filter['end_time'] = empty($_REQUEST['end_time']) ? '' : (strpos($_REQUEST['end_time'], '-') > 0 ?  local_strtotime($_REQUEST['end_time']) : $_REQUEST['end_time']);
	
	
	if (empty($filter['start_time']) || empty($filter['end_time'])){
		
		echo '<script>
			alert("请填写查询时间");
			 window.close();
			 </script>';
			// sys_msg("查询数据为空");

			exit;
	}
	
	/* 分页大小 */
	$filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);

	/*取得保存数据的数组*/
	$olists = empty($_REQUEST['lists']) ? '' : $_REQUEST['lists'];
	
	$lists =array();
	
	if($type == '1'){
		/*查询数据 一次只取100条数据*/
		$lists = get_point_export1($filter);
	}elseif($type == '2'){
		$lists = get_point_export2($filter);
	}elseif($type == '3'){
		$lists = get_point_export3($filter);
	}
	
	/*之前的值先反序列化*/
	if(!empty($olists)){

		$olists = unserialize(stripslashes($olists));

		/*file_put_contents('log1.php',"<?php\r\nreturn ".var_export($olists,true)."?>",FILE_APPEND);*/
		$newlists = array_merge($olists,$lists['list']);
		/*file_put_contents('log2.php',"<?php\r\nreturn ".var_export($newlists,true)."?>",FILE_APPEND);*/

	}else{

		$newlists = $lists['list'];

	}

	/*新的值序列化*/
	$newlists = serialize($newlists);

	$filter['page'] = $filter['page']+1;

	/*是否应该跳转*/
	if(($filter['page']) <= $lists['filter']['page_count']){

			$smarty->assign('yes', 1);

	}else{

			$smarty->assign('yes', 0);
	}
	
	$smarty->assign('lists', $newlists);

	$smarty->assign('filter', $filter);
	
	$smarty->assign('type', $type);

	$smarty->assign('act', "point_query");
	
	$smarty->display('export.htm');

	
}
elseif ($_REQUEST['act'] == 'export1')
{

		$lists = $_REQUEST['lists'];

		/*反序列化*/
		$point_export_list = unserialize(stripslashes($lists));

		/*获取退货订单数据*/
		// $refund_goods_list = get_refund_goods();

		if (empty($point_export_list))
			
		 {	
			echo '<script>
			 alert("查询数据为空");
			 window.close();
			 </script>';
			// sys_msg("查询数据为空");
			exit;
		}


		// 实例化excel类
		$objPHPExcel = new PHPExcel();
		// 操作第一个工作表
		$objPHPExcel->setActiveSheetIndex(0);
		// 设置sheet名
		$objPHPExcel->getActiveSheet()->setTitle('余额对账数据列表');
		// 设置表格宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
		
		// 列名表头文字加粗
		$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);
		// 列表头文字居中
		$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()
			->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 列名赋值
		$objPHPExcel->getActiveSheet()->setCellValue('A1', '客户编号');
		$objPHPExcel->getActiveSheet()->setCellValue('B1', '客户姓名');
		$objPHPExcel->getActiveSheet()->setCellValue('C1', '前值余额');
		$objPHPExcel->getActiveSheet()->setCellValue('D1', '充值金额');
		$objPHPExcel->getActiveSheet()->setCellValue('E1', '消费金额');
		$objPHPExcel->getActiveSheet()->setCellValue('F1', '后值余额');

		// 数据起始行
		$row_num = 2;
    // 向每行单元格插入数据
    foreach( $point_export_list as $k=>$value)
    {
		// print_r($value);
		// die;

        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'F' . $row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'F' . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置价格为数字格式
        $objPHPExcel->getActiveSheet()->getStyle('C' . $row_num . ':' . 'F' . $row_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);


        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['user_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['user_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['q_money']);
		$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['c_money']);
		$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['t_money']);
		$objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['h_money']);


        $row_num++;
    }
    $outputFileName = '余额对账数据列表' . date("Y/m/d") . '.xls';
    $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);

}
elseif ($_REQUEST['act'] == 'export2')
{

		$lists = $_REQUEST['lists'];

		/*反序列化*/
		$point_export_list2 = unserialize(stripslashes($lists));

		/*获取退货订单数据*/
		// $refund_goods_list = get_refund_goods();

		if (empty($point_export_list2))
			
		{	
			echo '<script>
			 alert("查询数据为空");
			 window.close();
			 </script>';
			// sys_msg("查询数据为空");
			exit;

		}


		// 实例化excel类
		$objPHPExcel = new PHPExcel();
		// 操作第一个工作表
		$objPHPExcel->setActiveSheetIndex(0);
		// 设置sheet名
		$objPHPExcel->getActiveSheet()->setTitle('积分对账数据列表');
		// 设置表格宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
		
		// 列名表头文字加粗
		$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);
		// 列表头文字居中
		$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()
			->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 列名赋值
		$objPHPExcel->getActiveSheet()->setCellValue('A1', '客户编号');
		$objPHPExcel->getActiveSheet()->setCellValue('B1', '客户姓名');
		$objPHPExcel->getActiveSheet()->setCellValue('C1', '前值积分');
		$objPHPExcel->getActiveSheet()->setCellValue('D1', '充值积分');
		$objPHPExcel->getActiveSheet()->setCellValue('E1', '消费积分');
		$objPHPExcel->getActiveSheet()->setCellValue('F1', '后值积分');

		// 数据起始行
		$row_num = 2;
    // 向每行单元格插入数据
    foreach( $point_export_list2 as $k=>$value)
    {
		// print_r($value);
		// die;

        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'F' . $row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'F' . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['user_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['user_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['q_money']);
		$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['c_money']);
		$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['t_money']);
		$objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['h_money']);


        $row_num++;
    }
    $outputFileName = '积分对账数据列表' . date("Y/m/d") . '.xls';
    $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);

}
elseif ($_REQUEST['act'] == 'export3')
{

		$lists = $_REQUEST['lists'];

		/*反序列化*/
		$point_export_list3 = unserialize(stripslashes($lists));

		/*获取退货订单数据*/
		// $refund_goods_list = get_refund_goods();

		if (empty($point_export_list3))
			
		 {	
			echo '<script>
			 alert("查询数据为空");
			 window.close();
			 </script>';
			// sys_msg("查询数据为空");
			exit;
		}


		// 实例化excel类
		$objPHPExcel = new PHPExcel();
		// 操作第一个工作表
		$objPHPExcel->setActiveSheetIndex(0);
		// 设置sheet名
		$objPHPExcel->getActiveSheet()->setTitle('余额对账数据列表');
		// 设置表格宽度
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);

		
		// 列名表头文字加粗
		$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->getFont()->setBold(true);
		// 列表头文字居中
		$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->getAlignment()
			->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 列名赋值
		$objPHPExcel->getActiveSheet()->setCellValue('A1', '客户编号');
		$objPHPExcel->getActiveSheet()->setCellValue('B1', '客户姓名');
		$objPHPExcel->getActiveSheet()->setCellValue('C1', '抵扣总金额');


		// 数据起始行
		$row_num = 2;
    // 向每行单元格插入数据
    foreach( $point_export_list3 as $k=>$value)
    {
		// print_r($value);
		// die;

        //水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'C' . $row_num)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        // 设置所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'C' . $row_num)->getAlignment()
            ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置价格为数字格式
        $objPHPExcel->getActiveSheet()->getStyle('C' . $row_num . ':' . 'C' . $row_num)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);


        // 设置单元格数值
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['user_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['user_name']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['type_money']);



        $row_num++;
    }
    $outputFileName = '红包对账数据列表' . date("Y/m/d") . '.xls';
    $xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="' . $outputFileName . '"');
    header("Content-Transfer-Encoding: binary");
    header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    $xlsWriter->save("php://output");
    echo file_get_contents($outputFileName);
}
elseif($_REQUEST['act'] == 'ajaxs'){

	print_r($_REQUEST);
	die;

}

/**
* 订单对账数据查询
*  $filter  搜索条件数据
*/

function  get_order_export($filter){

        $where = 'WHERE 1 ';

        if ($filter['order_sn'])
        {
            $where .= " AND o.order_sn LIKE '%" . mysql_like_quote($filter['order_sn']) . "%'";
        }

		if ($filter['suppliers_id']!=null)
        {
			$where .= " AND g.suppliers_id = '$filter[suppliers_id]'";
        }

        if ($filter['consignee'])
        {
            $where .= " AND o.consignee LIKE '%" . mysql_like_quote($filter['consignee']) . "%'";
        }
        if ($filter['email'])
        {
            $where .= " AND o.email LIKE '%" . mysql_like_quote($filter['email']) . "%'";
        }
        if ($filter['address'])
        {
            $where .= " AND o.address LIKE '%" . mysql_like_quote($filter['address']) . "%'";
        }
        if ($filter['zipcode'])
        {
            $where .= " AND o.zipcode LIKE '%" . mysql_like_quote($filter['zipcode']) . "%'";
        }
        if ($filter['tel'])
        {
            $where .= " AND o.tel LIKE '%" . mysql_like_quote($filter['tel']) . "%'";
        }
        if ($filter['mobile'])
        {
            $where .= " AND o.mobile LIKE '%" .mysql_like_quote($filter['mobile']) . "%'";
        }
        if ($filter['country'])
        {
            $where .= " AND o.country = '$filter[country]'";
        }
        if ($filter['province'])
        {
            $where .= " AND o.province = '$filter[province]'";
        }
        if ($filter['city'])
        {
            $where .= " AND o.city = '$filter[city]'";
        }
        if ($filter['district'])
        {
            $where .= " AND o.district = '$filter[district]'";
        }
        if ($filter['shipping_id'])
        {
            $where .= " AND o.shipping_id  = '$filter[shipping_id]'";
        }
        if ($filter['pay_id'])
        {
            $where .= " AND o.pay_id  = '$filter[pay_id]'";
        }
        if ($filter['order_status'] != -1)
        {
            $where .= " AND o.order_status  = '$filter[order_status]'";
        }
        if ($filter['shipping_status'] != -1)
        {
            $where .= " AND o.shipping_status = '$filter[shipping_status]'";
        }
        if ($filter['pay_status'] != -1)
        {
            $where .= " AND o.pay_status = '$filter[pay_status]'";
        }
        if ($filter['user_id'])
        {
            $where .= " AND o.user_id = '$filter[user_id]'";
        }
        if ($filter['user_name'])
        {
            $where .= " AND u.user_name LIKE '%" . mysql_like_quote($filter['user_name']) . "%'";
        }
        if ($filter['start_time'])
        {
            $where .= " AND o.add_time >= '$filter[start_time]'";
        }
        if ($filter['end_time'])
        {
            $where .= " AND o.add_time <= '$filter[end_time]'";
        }

		/* Y-新增 付款时间 条件判断*/
		 if ($filter['start_time_a'])
        {
            $where .= " AND o.pay_time >= '$filter[start_time_a]'";
        }
        if ($filter['end_time_a'])
        {
            $where .= " AND o.pay_time <= '$filter[end_time_a]'";
        }
		/* Y-新增 供应商发货时间 条件判断*/

		 // if ($filter['start_time_b'])
   //      {
   //          $where .= " AND o.pay_time >= '$filter[start_time_c]'";
   //      }
   //      if ($filter['end_time_b'])
   //      {
   //          $where .= " AND o.pay_time <= '$filter[end_time_c]'";
   //      }


		/* Y-新增 支付货款时间 条件判断*/

		 // if ($filter['start_time_c'])
   //      {
   //          $where .= " AND o.pay_time >= '$filter[start_time_c]'";
   //      }
   //      if ($filter['end_time_c'])
   //      {
   //          $where .= " AND o.pay_time <= '$filter[end_time_c]'";
   //      }

		/* Y-新增 订单完成时间 条件判断*/
		 if ($filter['start_time_d'])
        {
            $where .= " AND o.affirm_time >= '$filter[start_time_d]'";
        }
        if ($filter['end_time_d'])
        {
            $where .= " AND o.affirm_time <= '$filter[end_time_d]'";
        }

        //综合状态
        switch($filter['composite_status'])
        {
            case CS_AWAIT_PAY :
                $where .= order_query_sql('await_pay');
                break;

            case CS_AWAIT_SHIP :
                $where .= order_query_sql('await_ship');
                break;

            case CS_FINISHED :
                $where .= order_query_sql('finished');
                break;

            case PS_PAYING :
                if ($filter['composite_status'] != -1)
                {
                    $where .= " AND o.pay_status = '$filter[composite_status]' ";
                }
                break;
            case OS_SHIPPED_PART :
                if ($filter['composite_status'] != -1)
                {
                    $where .= " AND o.shipping_status  = '$filter[composite_status]'-2 ";
                }
                break;
            default:
                if ($filter['composite_status'] != -1)
                {
                    $where .= " AND o.order_status = '$filter[composite_status]' ";
                }
        }

        /* 团购订单 */
        if ($filter['group_buy_id'])
        {
            $where .= " AND o.extension_code = 'group_buy' AND o.extension_id = '$filter[group_buy_id]' ";
        }

        /* 如果管理员属于某个办事处，只列出这个办事处管辖的订单 */
        $sql = "SELECT agency_id FROM " . $GLOBALS['ecs']->table('admin_user') . " WHERE user_id = '$_SESSION[admin_id]'";
        $agency_id = $GLOBALS['db']->getOne($sql);
        if ($agency_id > 0)
        {
            $where .= " AND o.agency_id = '$agency_id' ";
        }

	    $join_goods = " LEFT JOIN " .$GLOBALS['ecs']->table('order_goods'). " AS og ON o.order_id=og.order_id LEFT JOIN " .$GLOBALS['ecs']->table('goods'). " AS g ON g.goods_id = og.goods_id ";

        /* 记录总数 */
        if ($filter['user_name'])
        {
            $sql = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('order_info') . " AS o ,".
                   $GLOBALS['ecs']->table('users') . " AS u " . $join_goods. $where;
        }
        else
        {
            $sql = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('order_info') . " AS o ". $join_goods. $where;
        }

        $filter['record_count']   = $GLOBALS['db']->getOne($sql);
        $filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / 100) : 1;

        /* 查询 */
        $sql = "SELECT  o.order_id, o.order_sn, o.add_time, o.shipping_fee, o.discount, o.surplus, o.integral, o.bonus, o.bonus_id, o.pay_time, o.pay_name, o.affirm_time,  o.order_status, o.shipping_status, o.order_amount, o.money_paid," . "o.pay_status, " . "(" . order_amount_field('o.') . ") AS total_fee, " . "IFNULL(u.user_name, '" .$GLOBALS['_LANG']['anonymous']. "') AS buyer ". " FROM " . $GLOBALS['ecs']->table('order_info') . " AS o " . " LEFT JOIN " .$GLOBALS['ecs']->table('users'). " AS u ON u.user_id=o.user_id ". $join_goods . $where ." LIMIT " . ($filter['page'] - 1) * 100 . ",100";
		// print_r($sql);
		// die;
        foreach (array('order_sn', 'consignee', 'email', 'address', 'zipcode', 'tel', 'user_name') AS $val)
        {
            $filter[$val] = stripslashes($filter[$val]);
        }
        set_filter($filter, $sql);

	$row = $GLOBALS['db']->getAll($sql);

    /* 格式化数据 */
    foreach ($row AS $key => $value)
    {
        $sql = "SELECT og.goods_id, og.goods_sn, og.goods_number, og.goods_price, g.shop_price, g.market_price  FROM " . $GLOBALS['ecs']->table('order_goods') . " AS og " . " LEFT JOIN " .$GLOBALS['ecs']->table('goods'). " AS g ON og.goods_id=g.goods_id "." WHERE og.order_id = '" . $value['order_id'] . "' ";

        $row[$key]['goods_list'] = $GLOBALS['db']->getAll($sql);

        // $row[$key]['formated_order_amount'] = price_format($value['order_amount']);
        // $row[$key]['formated_money_paid'] = price_format($value['money_paid']);
        // $row[$key]['formated_total_fee'] = price_format($value['total_fee']);
        $row[$key]['short_order_time'] = local_date('Y-m-d H:i:s', $value['add_time']);

		$row[$key]['short_pay_time'] = local_date('Y-m-d H:i:s', $value['pay_time']);
		$row[$key]['short_affirm_time'] = local_date('Y-m-d H:i:s', $value['affirm_time']);

        if ($value['order_status'] == OS_INVALID || $value['order_status'] == OS_CANCELED)
        {
            /* 如果该订单为无效或取消则显示删除链接 */
            $row[$key]['can_remove'] = 1;
        }
        else
        {
            $row[$key]['can_remove'] = 0;
        }

    }
    $arr = array('orders' => $row, 'filter' => $filter);
	// print_r($arr);
	// die;
    return $arr;
}

/**
* 退货订单数据查询
*/

function  get_refund_goods($filter){

		$where = 'WHERE 1 ';
       /*
        if ($filter['order_sn'])
        {
            $where .= " AND order_sn LIKE '%" . mysql_like_quote($filter['order_sn']) . "%'";
        }
        if ($filter['consignee'])
        {
            $where .= " AND consignee LIKE '%" . mysql_like_quote($filter['consignee']) . "%'";
        }
        if ($filter['delivery_sn'])
        {
            $where .= " AND delivery_sn LIKE '%" . mysql_like_quote($filter['delivery_sn']) . "%'";
        }
        */
        /* 获取管理员信息 */
        $admin_info = admin_info();

        /* 如果管理员属于某个办事处，只列出这个办事处管辖的发货单 */
        if ($admin_info['agency_id'] > 0)
        {
            $where .= " AND agency_id = '" . $admin_info['agency_id'] . "' ";
        }

        /* 如果管理员属于某个供货商，只列出这个供货商的发货单 */
        if ($admin_info['suppliers_id'] > 0)
        {
            $where .= " AND suppliers_id = '" . $admin_info['suppliers_id'] . "' ";
        }

        /* 分页大小 */
		/*
        $filter['page'] = empty($_REQUEST['page']) || (intval($_REQUEST['page']) <= 0) ? 1 : intval($_REQUEST['page']);

        if (isset($_REQUEST['page_size']) && intval($_REQUEST['page_size']) > 0)
        {
            $filter['page_size'] = intval($_REQUEST['page_size']);
        }
        elseif (isset($_COOKIE['ECSCP']['page_size']) && intval($_COOKIE['ECSCP']['page_size']) > 0)
        {
            $filter['page_size'] = intval($_COOKIE['ECSCP']['page_size']);
        }
        else
        {
            $filter['page_size'] = 15;
        }*/


        /* 记录总数 */
        $sql = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('back_order') . $where;

	   	$filter['record_count']   = $GLOBALS['db']->getOne($sql);
        $filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / 100) : 1;

        /* 查询 */
        //$sql = "SELECT o.back_id, o.user_id, o.delivery_sn, o.order_sn, o.order_id, o.add_time, o.action_user, o.consignee, o.country, o.province, o.city, o.district, o.tel, o.status, o.update_time, o.email, o.return_time, u.user_name FROM " . $GLOBALS['ecs']->table("back_order") . " AS o " . " LEFT JOIN " .$GLOBALS['ecs']->table('users'). " AS u ON u.user_id=o.user_id " . " $where ORDER BY " . $filter['sort_by'] . " " . $filter['sort_order'] . " LIMIT " . ($filter['page'] - 1) * $filter['page_size'] . ", " . $filter['page_size'] . " ";

		$sql = "SELECT o.back_id, o.user_id, o.delivery_sn, o.order_sn, o.order_id, o.add_time, o.action_user, o.consignee, o.country, o.province, o.city, o.district, o.tel, o.status, o.update_time, o.email, o.return_time, u.user_name FROM " . $GLOBALS['ecs']->table("back_order") . " AS o " . " LEFT JOIN " .$GLOBALS['ecs']->table('users'). " AS u ON u.user_id=o.user_id " . $where . " LIMIT " . ($filter['page'] - 1) * 100 . ", ". 100 ." ";

		//set_filter($filter, $sql);

		$row = $GLOBALS['db']->getAll($sql);

	/* 格式化数据 */
	foreach ($row AS $key => $value)
	{

		/*退货数量*/
		$sql = "SELECT  sum(og.goods_number) as goods_nums, " . "(" . order_amount_field('o.') . ") AS total_fee, og.goods_price FROM " . $GLOBALS['ecs']->table("order_info") . " AS o " . " LEFT JOIN " .$GLOBALS['ecs']->table('order_goods'). " AS og ON o.order_id=og.order_id where o.order_id = '".$value['order_id']."' ";

		$totals = $GLOBALS['db']->getRow($sql);

		$row[$key]['goods_nums'] =  $totals['goods_nums'];
		$row[$key]['back_amount'] =  $totals['total_fee'];
		$row[$key]['goods_price'] =  $totals['goods_price'];
		$row[$key]['goods_amount'] =  $totals['goods_price']*$totals['goods_nums'];

		$row[$key]['return_time'] = local_date($GLOBALS['_CFG']['time_format'], $value['return_time']);
		$row[$key]['add_time'] = local_date($GLOBALS['_CFG']['time_format'], $value['add_time']);
		$row[$key]['update_time'] = local_date($GLOBALS['_CFG']['time_format'], $value['update_time']);
		if ($value['status'] == 1)
		{
			$row[$key]['status_name'] = $GLOBALS['_LANG']['delivery_status'][1];
		}
		else
		{
		$row[$key]['status_name'] = $GLOBALS['_LANG']['delivery_status'][0];
		}
	}
		$arr = array('back' => $row, 'filter' => $filter);

		// print_r($arr);
		// die;
		return $arr;
}

/**
* 充值提现数据查询
*  keyword 会员名称  process_type类型   payment支付方式  is_paid 到款状态
*  start_date开始时间  end_date结束时间
*/
function get_account_export($filter){

	// $list = array();

	/* 过滤信息 */
	$where = 'WHERE 1 ';

	if ($filter['keywords'])
	{
		$where .= " AND u.user_name LIKE '%" . mysql_like_quote($filter['keywords']) . "%'";
	}

	if ($filter['payment'] != null)
	{
		$where .= " AND ua.payment = '$filter[payment]'";
	}

	if ($filter['process_type'] != -1)
	{
		$where .= " AND ua.process_type  = '$filter[process_type]'";
	}else{
        $where .= " AND ua.process_type " . db_create_in(array(SURPLUS_SAVE, SURPLUS_RETURN));
    }

	if ($filter['is_paid'] != -1)
	{
		$where .= " AND ua.is_paid  = '$filter[is_paid]'";
	}

	if ($filter['start_date'])
	{
		$where .= " AND ua.add_time >= '$filter[start_date]'";
	}
	if ($filter['end_date'])
	{
		$where .= " AND ua.add_time <= '$filter[end_date]'";
	}

	/* 记录总数 */

    $sql = 'SELECT COUNT(*) FROM ' .$GLOBALS['ecs']->table('user_account'). ' AS ua LEFT JOIN '. $GLOBALS['ecs']->table('users') . ' AS u ON ua.user_id=u.user_id ' . $where;

	$filter['record_count']   = $GLOBALS['db']->getOne($sql);

    $filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / 100) : 1;

	/* 查询数据 */
	$sql  = "SELECT ua.payment,u.user_name,ua.process_type,ua.is_paid,ua.add_time,ua.amount,ua.admin_user" . " FROM " . $GLOBALS['ecs']->table('user_account'). " AS ua " . " LEFT JOIN " . $GLOBALS['ecs']->table('users'). " AS u ON ua.user_id = u.user_id " . $where ." LIMIT " . ($filter['page'] - 1) * 100 . ",100";

	// $filter['keywords'] = stripslashes($filter['keywords']);
	// set_filter($filter, $sql);

    // print_r($sql);
	// die;

	$row = $GLOBALS['db']->getAll($sql);

foreach ($row AS $key => $value)
{
	$row[$key]['surplus_amount'] = price_format(abs($value['amount']), false);
	$row[$key]['add_date'] = local_date($GLOBALS['_CFG']['time_format'], $value['add_time']);
	$row[$key]['process_type_name'] = $GLOBALS['_LANG']['surplus_type_' . $value['process_type']];
}
    $arr = array('row' => $row, 'filter' => $filter);

    return $arr;
}

/**
* 充值及资金使用数据查询
*  user_id 客户编号  pay_code充值方式   start_time充值开始时间  end_time 结束时间
*  start_change_time使用开始时间  end_change_time结束时间
*/
function get_acc_export($filter){

	// $list = array();

	/* 过滤信息 */
	$where = " WHERE a.user_money <> 0 ";
        if ($filter['user_id'])
        {
            $where .= " AND a.user_id ='".$filter['user_id']."'";
        }

		if($filter['pay_code']){

			$where .= " AND a.change_type = 0 AND a.pay_code ='".$filter['pay_code']."'";

			if ($filter['start_time'])
			{
				$where .= " AND a.change_time >= '$filter[start_time]'";
			}
			if ($filter['end_time'])
			{
				$where .= " AND a.change_time <= '$filter[end_time]'";
			}

		}else if(!empty($filter['start_time']) && !empty($filter['end_time'])){

			$where .= " AND a.change_type = 0 ";

			if ($filter['start_time'])
			{
				$where .= " AND a.change_time >= '$filter[start_time]'";
			}
			if ($filter['end_time'])
			{
				$where .= " AND a.change_time <= '$filter[end_time]'";
			}


		}else if(!empty($filter['start_change_time']) && !empty($filter['end_change_time'])){

			$where .= " AND a.change_type = 99 ";

			if ($filter['start_change_time'])
			{
				$where .= " AND a.change_time >= '$filter[start_change_time]'";
			}
			if ($filter['end_change_time'])
			{
				$where .= " AND a.change_time <= '$filter[end_change_time]'";
			}

		}else{

			$where .= " AND a.change_type in(0,99) ";

		}

	/* 记录总数 */

    $sql = "SELECT COUNT(*) ". " FROM " . $GLOBALS['ecs']->table('account_log') . "AS a " . $where;

	$filter['record_count']   = $GLOBALS['db']->getOne($sql);

    $filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / 100) : 1;

	/* 查询数据 */

	$sql = "SELECT a.*, u.user_name " . " FROM " . $GLOBALS['ecs']->table('account_log') . "AS a LEFT JOIN " . $GLOBALS['ecs']->table('users') . " AS u on a.user_id=u.user_id ". $where ." LIMIT " . ($filter['page'] - 1) * 100 . ",100";

	$list = $GLOBALS['db']->getAll($sql);

    foreach ($list as $key => $item) {

        $list[$key]['change_time'] = date('Y-m-d h:m:s',$item['change_time']);

		$p_pay_code = $GLOBALS['db']->getOne("SELECT pay_name FROM " .$GLOBALS['ecs']->table('payment'). " WHERE pay_code = '".$item['pay_code']."' ");

		if(empty($p_pay_code)){

			$p_pay_code = $GLOBALS['db']->getOne("SELECT pay_name FROM " .$GLOBALS['ecs']->table('touch_payment'). " WHERE pay_code = '".$item['pay_code']."' ");

		}

		$list[$key]['pay_name'] = $p_pay_code;

    }

    $arr = array('list' => $list, 'filter' => $filter);

	// print_r($arr);
	// die;
    return $arr;

}

/**
* 余额对账数据查询
* $type 类型 1余额 2积分 3红包
* type查询类型   start_time开始时间  end_time 结束时间
*/
function get_point_export1($filter){

	/*前值时间*/
	$a_start_time = $filter['start_time']+16*3600;

	/*后值时间*/
	$a_end_time = $filter['end_time']+16*3600;
	
	$sql = "SELECT distinct user_id FROM ecs_account_log  WHERE user_money <> 0 and change_time >= '".$a_start_time."' and change_time <= '".$a_end_time."' ";
	// print_r($sql);
	// die;
	$row = $GLOBALS['db']->getAll($sql);
	// print_r($row);
	// die;
	
	/*用户ID 逗号隔开*/
	$new_row = '';
	if(!empty($row)){
		
		foreach($row as $key=>$value){
			
			if(!empty($value['user_id'])){
				$new_row .= $value['user_id'].",";
			}
		
		}
		
		//去除最后一个逗号
		$new_row = substr($new_row, 0, -1);
		
	}

	// echo $new_row;
	// echo "<pre>";
	// print_r($row);
	// echo "<pre>";
	// die;
	
	if(!empty($new_row)){
		

		/* 记录总数 */
		$sqlc = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) ";

		$filter['record_count']   = $GLOBALS['db']->getOne($sqlc);
		
		$filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / 100) : 1;
		
		
		/*根据users表查询数据*/
		$sqlu = "SELECT user_id, user_name FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) " . " LIMIT " . ($filter['page'] - 1) * 100 . ",100";

		$lists = $GLOBALS['db']->getAll($sqlu);

		foreach ($lists as $key => $item) {
			
			/*前值余额*/
			$q_money = $GLOBALS['db']->getOne("SELECT sum(a.user_money) as q_money FROM ecs_account_log AS a WHERE a.user_money <> 0 and a.change_time <= '".$a_start_time."' and a.user_id = '".$item['user_id']."' ");
			
			if(empty($q_money)){
				$lists[$key]['q_money'] = 0;
			}else{
				$lists[$key]['q_money'] = $q_money;
			}
			
			
			/*后值余额*/
			$h_money = $GLOBALS['db']->getOne("SELECT sum(b.user_money) as h_money FROM ecs_account_log AS b WHERE b.user_money <> 0 and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($h_money)){
				$lists[$key]['h_money'] = 0;
			}else{
				$lists[$key]['h_money'] = $h_money;
			}

			/*充值金额  不仅仅查询 b.change_type = 0 */
			$c_money = $GLOBALS['db']->getOne("SELECT sum(b.user_money) as c_money FROM ecs_account_log AS b WHERE b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_money > 0  and b.user_id = '".$item['user_id']."' ");
			
			if(empty($c_money)){
				$lists[$key]['c_money'] = 0;
			}else{
				$lists[$key]['c_money'] = $c_money;
			}
			
			
			/*消费金额*/
			$t_money = $GLOBALS['db']->getOne("SELECT sum(b.user_money) as t_money FROM ecs_account_log AS b WHERE b.user_money < 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($t_money)){
				$lists[$key]['t_money'] = 0;
			}else{
				$lists[$key]['t_money'] = $t_money;
			}

			/*详细数据*/
			$list_money = $GLOBALS['db']->getAll("SELECT * FROM ecs_account_log AS b WHERE b.user_money <> 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");	
			
			
			foreach($list_money as $k=>$v){
				
				$list_money[$k]['time'] = local_date('Y-m-d H:i:s',$v['change_time']);
			}
			
			$lists[$key]['list_money'] = $list_money;
		
		}
		
	
	}
	
    $arr = array('list' => $lists, 'filter' => $filter);
	// print_r($arr);
	// die;	
    return $arr;
}

/**
* 积分对账数据查询
*/
function get_point_export2($filter){

	/*前值时间*/
	$a_start_time = $filter['start_time']+16*3600;

	/*后值时间*/
	$a_end_time = $filter['end_time']+16*3600;

	$sql = "SELECT distinct user_id FROM ecs_account_log  WHERE pay_points <> 0 and change_time >= '".$a_start_time."' and change_time <= '".$a_end_time."' ";
	
	$row = $GLOBALS['db']->getAll($sql);
	
	/*用户ID 逗号隔开*/
	$new_row = '';
	if(!empty($row)){
		
		foreach($row as $key=>$value){
			
			if(!empty($value['user_id'])){
				$new_row .= $value['user_id'].",";
			}
		
		}
		
		//去除最后一个逗号
		$new_row = substr($new_row, 0, -1);
		
	}

	// echo $new_row;
	// echo "<pre>";
	// print_r($row);
	// echo "<pre>";
	// die;
	if(!empty($new_row)){
		
		$sqlc = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) ";
		
		$filter['record_count']   = $GLOBALS['db']->getOne($sqlc);
		$filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / 100) : 1;
		
		
		/*根据users表查询数据*/
		$sqlu = "SELECT user_id, user_name FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) " . " LIMIT " . ($filter['page'] - 1) * 100 . ",100";

		$lists = $GLOBALS['db']->getAll($sqlu);
		
		foreach ($lists as $key => $item) {
			
			/*前值余额*/
			$q_money = $GLOBALS['db']->getOne("SELECT sum(a.pay_points) as q_money FROM ecs_account_log AS a WHERE a.pay_points <> 0 and a.change_time <= '".$a_start_time."' and a.user_id = '".$item['user_id']."' ");
			
			if(empty($q_money)){
				$lists[$key]['q_money'] = 0;
			}else{
				$lists[$key]['q_money'] = $q_money;
			}
			
			
			/*后值余额*/
			$h_money = $GLOBALS['db']->getOne("SELECT sum(b.pay_points) as h_money FROM ecs_account_log AS b WHERE b.pay_points <> 0 and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($h_money)){
				$lists[$key]['h_money'] = 0;
			}else{
				$lists[$key]['h_money'] = $h_money;
			}

			/*充值金额  不仅仅查询 b.change_type = 0 */
			$c_money = $GLOBALS['db']->getOne("SELECT sum(b.pay_points) as c_money FROM ecs_account_log AS b WHERE b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.pay_points > 0  and b.user_id = '".$item['user_id']."' ");
			
			if(empty($c_money)){
				$lists[$key]['c_money'] = 0;
			}else{
				$lists[$key]['c_money'] = $c_money;
			}

			/*消费金额*/
			$t_money = $GLOBALS['db']->getOne("SELECT sum(b.pay_points) as t_money FROM ecs_account_log AS b WHERE b.pay_points < 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");
			
			if(empty($t_money)){
				$lists[$key]['t_money'] = 0;
			}else{
				$lists[$key]['t_money'] = $t_money;
			}

			/*详细数据*/
			$list_money = $GLOBALS['db']->getAll("SELECT * FROM ecs_account_log AS b WHERE b.pay_points <> 0 and b.change_time >= '".$a_start_time."' and b.change_time <= '".$a_end_time."' and b.user_id = '".$item['user_id']."' ");	

			foreach($list_money as $k=>$v){
				
				$list_money[$k]['time'] = local_date('Y-m-d H:i:s',$v['change_time']);
			}
			
			$lists[$key]['list_money'] = $list_money;
		
		}
	
    }
    $arr = array('list' => $lists, 'filter' => $filter);
		
    return $arr;

}

/**
* 红包对账数据查询
*/
function get_point_export3($filter){

	/*前值时间*/
	$a_start_time = $filter['start_time']+16*3600;

	/*后值时间*/
	$a_end_time = $filter['end_time']+16*3600;
	
	$sql = "SELECT distinct user_id FROM ecs_user_bonus WHERE used_time != 0 and used_time >= '".$a_start_time."' and used_time <= '".$a_end_time."' ";
	
	$row = $GLOBALS['db']->getAll($sql);
	
	/*用户ID 逗号隔开*/
	$new_row = '';
	if(!empty($row)){
		
		foreach($row as $key=>$value){
			
			if(!empty($value['user_id'])){
				$new_row .= $value['user_id'].",";
			}
		
		}
		
		//去除最后一个逗号
		$new_row = substr($new_row, 0, -1);
		
	}

	// echo $new_row;
	// echo "<pre>";
	// print_r($row);
	// echo "<pre>";
	// die;
	if(!empty($new_row)){
		
		$sqlc = "SELECT COUNT(*) FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) ";
		
		$filter['record_count']   = $GLOBALS['db']->getOne($sqlc);
		$filter['page_count']     = $filter['record_count'] > 0 ? ceil($filter['record_count'] / 100) : 1;
		
		
		/*根据users表查询数据*/
		$sqlu = "SELECT user_id, user_name FROM " . $GLOBALS['ecs']->table('users') . " where user_id in($new_row) " . " LIMIT " . ($filter['page'] - 1) * 100 . ",100";

		$lists = $GLOBALS['db']->getAll($sqlu);
		
		foreach ($lists as $key => $item) {
			
			/*红包总抵扣值*/
			$bonus_list = $GLOBALS['db']->getAll("SELECT * FROM ecs_user_bonus AS a WHERE a.used_time != 0 and a.used_time >= '".$a_start_time."'  and a.used_time <= '".$a_end_time."' and a.user_id = '".$item['user_id']."' ");
			
			$type_money = 0;
			
			if(!empty($bonus_list)){
				
				foreach($bonus_list as $k=>$value){
					
					$type_money += $GLOBALS['db']->getOne("SELECT type_money FROM ecs_bonus_type  WHERE  type_id = '".$value['bonus_type_id']."' ");	
					
					$bonus_list[$k]['time'] = local_date('Y-m-d H:i:s',$value['used_time']);
				}
			}
			
			$lists[$key]['type_money'] = $type_money;
			
			/*详细数据*/
			$lists[$key]['bonus_list'] = $bonus_list;
		
		}
    }
	
    $arr = array('list' => $lists, 'filter' => $filter);
		
    return $arr;

}

?>